﻿using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;
using QD.DbManager.DbContent;
using QD.SQLHelper;
using QD.Tools;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace QD.DbManager
{
    public partial class QDSEditor : DbBasic
    {
        int maxTPIndex = 1;

        //DbConfigData dbConfigContext;
        DbData dbDataContext;
        DbQfDevPlat dbQfDevPlat;

        DbConfig dbConfig;

        TextEditorControl curTextEditor;

        public QDSEditor(DbConfig dbConfig)
        {
            InitializeComponent();
            maxTPIndex = 1;

            this.dbConfig = dbConfig;
            //this.dbConfigContext = new DbConfigData(dbConfig.GetDbConfigConnStr());
            this.dbDataContext = new DbData(dbConfig.GetDbMasterConnStr());
            this.dbQfDevPlat = new DbQfDevPlat(dbConfig.GetDbDataBaseConnStr());

            curTextEditor = this.texteditor1;
        }

        private void QDSEditor_Load(object sender, EventArgs e)
        {
            this.KeyPreview = true;
            this.KeyDown += BaseForm_KeyDown;

            this.texteditor1.ShowEOLMarkers = false;
            this.texteditor1.ShowHRuler = false;
            this.texteditor1.ShowInvalidLines = false;
            this.texteditor1.ShowMatchingBracket = true;
            this.texteditor1.ShowSpaces = false;
            this.texteditor1.ShowTabs = false;
            this.texteditor1.ShowVRuler = false;
            this.texteditor1.AllowCaretBeyondEOL = false;
            this.texteditor1.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            this.texteditor1.Encoding = Encoding.GetEncoding("GB2312");

            this.db_tree.ImageList = new ImageList();
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.tick);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.table);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.table_analysis);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.function);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.data_table);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.gcp);
            this.db_tree.ImageList.Images.Add(QD.DbManager.Properties.Resources.file_extension_3gp);

            this.tabControlQuery.SelectedIndexChanged += tabControlQuery_SelectedIndexChanged;
            this.db_tree.ContextMenuStrip = this.contextMenuStrip1;
            this.grid_result.ContextMenuStrip = this.contextMenuStrip2;

            this.grid_cmd.InitGrid(false, false, true);
            ContextMenuStrip strip = this.grid_cmd.RowTemplate.ContextMenuStrip;
            ToolStripMenuItem menu = new ToolStripMenuItem("删除选中命令");
            menu.Click += menu_Click;
            strip.Items.Add(menu);

            btn_refresh_Click(sender, e);

            Bind_UCommands();
        }

        void menu_Click(object sender, EventArgs e)
        {
            if (PromptHelper.PromptOkCancel("您确认删除所有选中的命令？") == DialogResult.OK)
            {
                try
                {
                    IList<int> selectRowsIndexs = this.grid_cmd.GetSelectRowsIndex();

                    foreach (int index in selectRowsIndexs)
                    {
                        string _cmdTitle = this.grid_cmd.Rows[index].GetCellValueToString("CmdTitle");
                        QDS_UCommands mod = dbQfDevPlat.QDS_UCommands.FirstOrDefault(p => p.CmdTitle == _cmdTitle);
                        if (mod != null)
                        {
                            dbQfDevPlat.QDS_UCommands.Remove(mod);
                        }
                    }
                    dbQfDevPlat.SaveChanges();
                    Bind_UCommands();
                    Tools.PromptHelper.Prompt("删除成功！");
                }
                catch (Exception ex)
                {
                    PromptHelper.Prompt(ex);
                }
            }
        }

        private void Bind_UCommands()
        {
            List<QDS_UCommands> list = dbQfDevPlat.QDS_UCommands.Where(p => p.Uid == dbConfig.Uid).OrderBy(p => p.CreateDate).ToList();
            this.grid_cmd.DataSource = list;
        }

        private void BaseForm_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                toolStripButton1_Click(sender, e);
            }
            else if (e.KeyCode == Keys.S && e.Modifiers == Keys.Control)
            {
                saveCmd_Click(sender, e);
            }
        }

        void tabControlQuery_SelectedIndexChanged(object sender, EventArgs e)
        {
            TabPage tp = this.tabControlQuery.SelectedTab;
            if (tp == null)
            {
                curTextEditor = null;
            }
            else
            {
                foreach (Control ctl in tp.Controls)
                {
                    if (ctl is TextEditorControl)
                    {
                        curTextEditor = (TextEditorControl)ctl;
                    }
                }
            }
        }

        private void btn_refresh_Click(object sender, EventArgs e)
        {
            LoadDb_Tree("");
        }

        /// <summary>
        /// 加载数据库结构
        /// </summary>
        private void LoadDb_Tree(string txtKey)
        {
            try
            {
                this.db_tree.Nodes.Clear();

                #region 表
                TreeNode node_T = new TreeNode("表");
                node_T.ImageIndex = 1;
                this.db_tree.Nodes.Add(node_T);

                string sql_T = "";
                if (string.IsNullOrEmpty(txtKey))
                {
                    sql_T = string.Format("select a.name,b.TableName from sys.tables a left join [{0}]..FormTable b on a.name=b.TableCode order by a.create_date asc", dbConfig.DbConfigName);
                }
                else
                {
                    sql_T = string.Format("select a.name,b.TableName from sys.tables a left join [{0}]..FormTable b on a.name=b.TableCode where a.name like '%{1}%' or b.TableName like '%{1}%' order by a.create_date asc", dbConfig.DbConfigName, txtKey);
                }

                DataTable dt_T = dbDataContext.Query(sql_T);
                foreach (DataRow dr in dt_T.Rows)
                {
                    TreeNode t_c_node = null;
                    if (dr["TableName"] != DBNull.Value)
                    {
                        t_c_node = new TreeNode(dr["name"].ToString() + " [" + dr["TableName"].ToString() + "]");
                    }
                    else
                    {
                        t_c_node = new TreeNode(dr["name"].ToString());
                    }
                    t_c_node.ImageIndex = 1;
                    t_c_node.Tag = new string[] { "T", dr["name"].ToString() };
                    node_T.Nodes.Add(t_c_node);
                }
                #endregion

                #region 视图
                TreeNode node_V = new TreeNode("视图");
                node_V.ImageIndex = 2;
                this.db_tree.Nodes.Add(node_V);

                string sql_V = "";
                if (string.IsNullOrEmpty(txtKey))
                {
                    sql_V = string.Format("select a.name,b.TableName from sys.views a left join [{0}]..FormTable b on a.name=b.TableCode order by a.create_date asc", dbConfig.DbConfigName);
                }
                else
                {
                    sql_V = string.Format("select a.name,b.TableName from sys.views a left join [{0}]..FormTable b on a.name=b.TableCode where a.name like '%{1}%' or b.TableName like '%{1}%' order by a.create_date asc", dbConfig.DbConfigName, txtKey);
                }
                DataTable dt_V = dbDataContext.Query(sql_V);
                foreach (DataRow dr in dt_V.Rows)
                {
                    TreeNode t_v_node = null;
                    if (dr["TableName"] != DBNull.Value)
                    {
                        t_v_node = new TreeNode(dr["name"].ToString() + " [" + dr["TableName"].ToString() + "]");
                    }
                    else
                    {
                        t_v_node = new TreeNode(dr["name"].ToString());
                    }
                    t_v_node.ImageIndex = 2;
                    t_v_node.Tag = new string[] { "V", dr["name"].ToString() };
                    node_V.Nodes.Add(t_v_node);
                }
                #endregion

                #region 触发器
                TreeNode node_TR = new TreeNode("触发器");
                node_TR.ImageIndex = 5;
                this.db_tree.Nodes.Add(node_TR);

                string sql_TR = "";
                if (string.IsNullOrEmpty(txtKey))
                {
                    sql_TR = "select name from sys.triggers order by create_date asc";
                }
                else
                {
                    sql_TR = string.Format("select name from sys.triggers where name like '%{0}%' order by create_date asc", txtKey);
                }
                DataTable dt_TR = dbDataContext.Query(sql_TR);
                foreach (DataRow dr in dt_TR.Rows)
                {
                    TreeNode t_tr_node = new TreeNode(dr["name"].ToString());
                    t_tr_node.Tag = new string[] { "TR", dr["name"].ToString() };
                    t_tr_node.ImageIndex = 5;
                    node_TR.Nodes.Add(t_tr_node);
                }
                #endregion

                #region 存储过程
                TreeNode node_P = new TreeNode("存储过程");
                node_P.ImageIndex = 4;
                this.db_tree.Nodes.Add(node_P);

                string sql_P = "";
                if (string.IsNullOrEmpty(txtKey))
                {
                    sql_P = "select name from sys.procedures order by create_date asc";
                }
                else
                {
                    sql_P = string.Format("select name from sys.procedures where name like '%{0}%' order by create_date asc", txtKey);
                }
                DataTable dt_P = dbDataContext.Query(sql_P);
                foreach (DataRow dr in dt_P.Rows)
                {
                    TreeNode t_p_node = new TreeNode(dr["name"].ToString());
                    t_p_node.Tag = new string[] { "P", dr["name"].ToString() };
                    t_p_node.ImageIndex = 4;
                    node_P.Nodes.Add(t_p_node);
                }
                #endregion

                #region 函数
                TreeNode node_F = new TreeNode("函数");
                node_F.ImageIndex = 3;
                this.db_tree.Nodes.Add(node_F);

                string sql_F = "";
                if (string.IsNullOrEmpty(txtKey))
                {
                    sql_F = "select name from sys.objects where type='TF' or type='FN' order by create_date asc";
                }
                else
                {
                    sql_F = string.Format("select name from sys.objects where (type='TF' or type='FN') and name like '%{0}' order by create_date asc", txtKey);
                }
                DataTable dt_F = dbDataContext.Query(sql_F);
                foreach (DataRow dr in dt_F.Rows)
                {
                    TreeNode t_f_node = new TreeNode(dr["name"].ToString());
                    t_f_node.Tag = new string[] { "F", dr["name"].ToString() };
                    t_f_node.ImageIndex = 3;
                    node_F.Nodes.Add(t_f_node);
                }
                #endregion

                if (string.IsNullOrEmpty(this.txtKey.Text.Trim()))
                {
                    this.db_tree.CollapseAll();
                }
                else
                {
                    this.db_tree.ExpandAll();
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void txtKey_TextChanged(object sender, EventArgs e)
        {
            LoadDb_Tree(this.txtKey.Text.Trim());
        }

        private void addQuery_Click(object sender, EventArgs e)
        {
            AddNewTextEditor("", "");
        }

        public void AddNewTextEditor(string cmdText, string cmdTitle)
        {
            maxTPIndex++;

            string tpText = "查询" + maxTPIndex.ToString();
            string tpName = "Query" + maxTPIndex.ToString();
            string texteditorName = "texteditor" + maxTPIndex.ToString();

            TextEditorControl c = new TextEditorControl();
            c.Name = texteditorName;
            c.Dock = DockStyle.Fill;
            c.ShowEOLMarkers = false;
            c.ShowHRuler = false;
            c.ShowInvalidLines = false;
            c.ShowMatchingBracket = true;
            c.ShowSpaces = false;
            c.ShowTabs = false;
            c.ShowVRuler = false;
            c.AllowCaretBeyondEOL = false;
            c.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            c.Encoding = Encoding.GetEncoding("GB2312");
            c.Text = cmdText;
            c.Tag = cmdTitle;
            curTextEditor = c;

            TabPage tp = new TabPage(tpText);
            tp.Name = tpName;
            tp.Controls.Add(c);

            this.tabControlQuery.TabPages.Add(tp);
            this.tabControlQuery.SelectTab(tpName);
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            DialogResult isOk = DialogResult.OK;
            string sql = curTextEditor.ActiveTextAreaControl.SelectionManager.SelectedText;
            if (string.IsNullOrEmpty(sql))
            {
                sql = curTextEditor.Text.Trim();
            }

            if (string.IsNullOrEmpty(sql))
            {
                Tools.PromptHelper.Prompt("请输入sql命令！");
            }
            else
            {
                if ((sql.ToLower().Contains("update ")
                   || sql.ToLower().Contains("insert ")
                   || sql.ToLower().Contains("delete ")))
                {
                    isOk = Tools.PromptHelper.PromptOkCancel("确定执行sql命令？");
                }

                if (isOk == DialogResult.OK)
                {
                    try
                    {
                        DateTime time1 = DateTime.Now;

                        this.laResult.Text = "命令执行中，请稍候……";
                        this.laResult.Update();
                        DataTable dt = dbDataContext.Query(sql);
                        if (dt.Rows.Count > 10000)
                        {
                            DataTable _dt = dt.GetTopData(10000);
                            this.grid_result.DataSource = _dt;
                        }
                        else
                        {
                            this.grid_result.DataSource = dt;
                        }

                        DateTime time2 = DateTime.Now;
                        TimeSpan ts = time2 - time1;
                        string timeLong = string.Format("{0}分{1}秒{2}毫秒", ts.Minutes, ts.Seconds, ts.Milliseconds);
                        this.laResult.Text = string.Format("命令执行完成，共{0}条数据，执行时长{1}！", dt.Rows.Count, timeLong);

                    }
                    catch (Exception ex)
                    {
                        Tools.PromptHelper.Prompt(ex);
                    }
                }
            }
        }

        private void db_tree_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            try
            {
                TreeNode node = e.Node;
                if (node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];

                    curTextEditor.ActiveTextAreaControl.TextArea.InsertString(" " + _name);
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void 复制ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            this.db_tree.CopyFirstSelectedNodeText();
        }

        private void 复制列至查询窗口ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                TreeNode node = this.db_tree.SelectedNode;
                if (node != null && node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];
                    switch (_type)
                    {
                        case "T":
                        case "V":
                            {
                                string _name1 = _name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];
                                StringBuilder fields = GetTableFields(_name1);

                                curTextEditor.ActiveTextAreaControl.TextArea.InsertString(fields.ToString());
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void 生成查询SQLToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                TreeNode node = this.db_tree.SelectedNode;
                if (node != null && node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];
                    switch (_type)
                    {
                        case "T":
                        case "V":
                            {
                                string _name1 = _name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];
                                StringBuilder fields = GetTableFields(_name1);

                                string querySql = string.Format("select \n{0} \nfrom {1}", fields.ToString(), _name1);
                                curTextEditor.ActiveTextAreaControl.TextArea.InsertString(querySql);
                                break;
                            }
                        default:
                            {
                                Tools.PromptHelper.Prompt("选中项不支持查询功能！");
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private StringBuilder GetTableFields(string table)
        {
            string sql1 = string.Format("select name from sys.columns where object_id=object_id('{0}')", table);
            StringBuilder fields = new StringBuilder();
            DataTable dt = dbDataContext.Query(sql1);
            foreach (DataRow dr in dt.Rows)
            {
                if (fields.Length > 0)
                {
                    fields.Append(" , ");
                    fields.Append(" \n ");
                }
                else
                {
                    fields.Append(" ");
                }
                fields.Append(dr["name"].ToString());
            }
            return fields;
        }

        private void 查看列ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                TreeNode node = this.db_tree.SelectedNode;
                if (node != null && node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];
                    switch (_type)
                    {
                        case "T":
                        case "V":
                            {
                                string _name1 = _name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];

                                string sql = string.Format("select a.name,b.ColumnTitle from sys.columns a left join [{0}]..FormTableDetail b on OBJECT_NAME(a.object_id)=b.TableCode and a.name=b.ColumnCode where a.object_id=object_id('{1}') order by b.ID asc", dbConfig.DbConfigName, _name1);
                                DataTable dt = dbDataContext.Query(sql);

                                foreach (DataRow dr in dt.Rows)
                                {
                                    TreeNode _node = null;
                                    if (dr["ColumnTitle"] != DBNull.Value)
                                    {
                                        _node = new TreeNode(dr["name"].ToString() + " [" + dr["ColumnTitle"].ToString() + "]");
                                    }
                                    else
                                    {
                                        _node = new TreeNode(dr["name"].ToString());
                                    }
                                    _node.ImageIndex = 6;
                                    _node.Tag = new string[] { "Field", dr["name"].ToString() };
                                    node.Nodes.Add(_node);
                                }
                                node.ExpandAll();
                                break;
                            }
                        default:
                            {
                                Tools.PromptHelper.Prompt("选中项不支持查看列功能！");
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void 查看定义ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                TreeNode node = this.db_tree.SelectedNode;
                if (node != null && node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];
                    switch (_type)
                    {
                        case "T":
                            {
                                Tools.PromptHelper.Prompt("选中项不支持查看定义！");
                                break;
                            }
                        default:
                            {
                                string _name1 = _name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];

                                string sql = string.Format("select definition from sys.SQL_Modules where object_id=object_id('{0}')", _name1);
                                object result = dbDataContext.ExecuteScalar(sql);

                                curTextEditor.ActiveTextAreaControl.TextArea.InsertString(result.ToString());
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void 导出前10000条数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                TreeNode node = this.db_tree.SelectedNode;
                if (node != null && node.Tag != null)
                {
                    string[] _value = (string[])node.Tag;
                    string _type = _value[0];
                    string _name = _value[1];
                    switch (_type)
                    {
                        case "T":
                        case "V":
                            {
                                string sql = "select top 10000 * from " + _name;

                                DataTable dt = dbDataContext.Query(sql);
                                dt.TableName = _name;
                                SaveFileMag frm = new SaveFileMag(dt);
                                frm.ShowDialog();
                                break;
                            }
                        default:
                            {
                                Tools.PromptHelper.Prompt("选中项不支持导出数据！");
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void 复制单元格ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            this.grid_result.CopyFirstSelectedCellValue();
        }

        private void 复制所有选中单元格ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.grid_result.CopySelectedCellsValue();
        }

        private void saveCmd_Click(object sender, EventArgs e)
        {
            try
            {
                string _cmdTitle = "";
                if (curTextEditor.Tag != null)
                {
                    _cmdTitle = curTextEditor.Tag.ToString();
                }

                OpenWindowSimpleFrm frm = new OpenWindowSimpleFrm(_cmdTitle);
                frm.Text = "请输入命令的标题";
                frm.SureBtn.Text = "保存";
                frm.SureEvent += frm_SureEvent;
                frm.Show();
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        void frm_SureEvent(object sourceData, object targetData)
        {
            try
            {
                string _cmdTitle = targetData.ToString().Trim();
                string _cmdContent = curTextEditor.Text.Trim();

                if (string.IsNullOrEmpty(_cmdTitle))
                {
                    throw new Exception("命令标题不能为空！");
                }
                else if (string.IsNullOrEmpty(_cmdContent))
                {
                    throw new Exception("命令内容不能为空！");
                }

                QDS_UCommands mod = dbQfDevPlat.QDS_UCommands.FirstOrDefault(p => p.CmdTitle == _cmdTitle);
                if (mod == null)
                {
                    mod = new QDS_UCommands();
                    mod.CmdTitle = _cmdTitle;
                    mod.Uid = dbConfig.Uid;
                    mod.CreateDate = DateTime.Now;
                    dbQfDevPlat.QDS_UCommands.Add(mod);
                }
                mod.CmdContent = _cmdContent;

                dbQfDevPlat.SaveChanges();
                Tools.PromptHelper.Prompt("保存成功！");
                Bind_UCommands();
            }
            catch (Exception ex)
            {
                Tools.PromptHelper.Prompt(ex);
            }
        }

        private void grid_cmd_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1 && e.ColumnIndex > -1)
            {
                string _cmdTitle = this.grid_cmd.Rows[e.RowIndex].Cells["CmdTitle"].Value.ToString();
                QDS_UCommands mod = dbQfDevPlat.QDS_UCommands.FirstOrDefault(p => p.CmdTitle == _cmdTitle);
                if (mod != null)
                {
                    AddNewTextEditor(mod.CmdContent, mod.CmdTitle);
                }
            }
        }

        private void 导出数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)this.grid_result.DataSource;
            dt.TableName = "DownloadData";
            if (dt != null && dt.Rows.Count > 0)
            {
                SaveFileMag frm = new SaveFileMag(dt);
                frm.ShowDialog();
            }
        }
    }
}
